<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							try{
							// Begin transaction
							conn.setAutoCommit(false);
							//insert section
							PreparedStatement pstmt = conn.prepareStatement(
					                "INSERT INTO meeting VALUES (?, ?, ?, ?, ?, ?, ?)");
							pstmt.setInt(1, Integer.parseInt(request.getParameter("section_id")));   
							String type = request.getParameter("type");
	                        pstmt.setString(2, type);
							String madatory = type.equals("LE") ? "YES" : request.getParameter("is_mandatory");
							pstmt.setString(3, madatory); 	
							pstmt.setInt(4, Integer.parseInt(request.getParameter("start_time")));
							pstmt.setInt(5, Integer.parseInt(request.getParameter("end_time")));
							pstmt.setString(6, request.getParameter("weekday")); 	
							pstmt.setString(7, request.getParameter("location")); 	
					        pstmt.executeUpdate();  

							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
							
							}catch (SQLException e) {
								out.println(e.getMessage());
								conn.rollback();								
							}
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							try{
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the course attributes in the course table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE meeting SET type = ?, is_mandatory = ?, start_time = ?, end_time = ?, weekday = ?, location = ? WHERE meeting_id = ?");
							String type = request.getParameter("type");
	                        pstmt.setString(1, type);
							String madatory = type.equals("LE") ? "YES" : request.getParameter("is_mandatory");
							pstmt.setString(2, madatory); 	
							pstmt.setInt(3, Integer.parseInt(request.getParameter("start_time")));
							pstmt.setInt(4, Integer.parseInt(request.getParameter("end_time")));
							pstmt.setString(5, request.getParameter("weekday")); 	
							pstmt.setString(6, request.getParameter("location")); 	
							pstmt.setInt(7, Integer.parseInt(request.getParameter("meeting_id")));   
							
	                        pstmt.executeUpdate();
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
							}catch (SQLException e) {
								out.println(e.getMessage());
								conn.rollback();								
							}
						}
						
						if (action != null && action.equals("delete")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        PreparedStatement pstmt = conn.prepareStatement("DELETE FROM meeting WHERE meeting_id = ?");
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("meeting_id"))); 
	                        pstmt.executeUpdate();
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
					
						// Create the statement
						Statement statement = conn.createStatement();
						
						
						//obtain all the section information 
						ResultSet rsSection = statement.executeQuery("SELECT * FROM section ORDER BY section_id");
						CachedRowSet cachedSection = new CachedRowSetImpl();
						cachedSection.populate(rsSection);	
						//List<Integer> sectionList = new LinkedList<Integer>();
						//while(rsSection.next())
						//	sectionList.add(rsSection.getInt("section_id"));
						
						
						//obtain the current section information	
						ResultSet rsMeeting = statement.executeQuery("SELECT * FROM meeting");
						CachedRowSet cachedMeeting = new CachedRowSetImpl();
						cachedMeeting.populate(rsMeeting);
						
				%>
				<table border="1">
					<tr>
						
						<th>Class</th>			
						<th>Meeting Type</th>
						<th>Discussion Mandatory</th>
						<th>Start Time</th>
						<th>End Time</th>
						<th>Weekdays</th>
						<th>Location</th>
					</tr>
					<tr>
						<form action="meeting.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<td> 
								<select name="section_id">
								<%
								while(cachedSection.next()){
									PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM class WHERE class_id=?");
									pstmt.setInt(1,cachedSection.getInt("class_id"));
									ResultSet rsClass = pstmt.executeQuery();
									if(rsClass.next()){
									String course_number = rsClass.getString("course_number");
									String title = rsClass.getString("title");
									String quarter = rsClass.getString("quarter");
									int year = rsClass.getInt("year");
								%>
								<option value="<%=cachedSection.getInt("section_id")%>">
									Section_ID:<%=cachedSection.getInt("section_id")%> <%=course_number%> <%=title%>(<%=quarter%> <%=year%>)
								</option>
									<% 
									}
									%>	
								<%
								}
								%>	
								</select>
							</td>	
							
							<td>
								<select name="type" style="width:100px">	
									<option value="LE">LE</option>
									<option value="DI">DI</option>
									<option value="LAB">LAB</option>
								</select>
							</td>
							
							<td>
								<select name="is_mandatory" style="width:100px">	
									<option value="YES">YES</option>
									<option value="NO">NO</option>
								</select>
							</td>			
							<td><input type="text" value="" name="start_time" size="10"></td>
							<td><input type="text" value="" name="end_time" size="10"></td>
							<td><input type="text" value="" name="weekday" size="10"></td>
							<td><input type="text" value="" name="location" size="10"></td>
							<td><input type="submit" value="Insert"></td>
						</form>
					</tr>
					
							
							<% 
							
                     			while(cachedMeeting.next()){		
                         	%> 
                         	<tr>	
                     			<form action="meeting.jsp" method="POST">
                        		<input type="hidden" value="update" name="action">                        
                         		<input type="hidden" value="<%=cachedMeeting.getInt("meeting_id")%>" name="meeting_id">
                         		
                         		<td>
                         		
                         		
              					<%
              			
									PreparedStatement pstmt = conn.prepareStatement("SELECT class_id FROM section WHERE section_id=?");
									pstmt.setInt(1,cachedMeeting.getInt("section_id"));
									ResultSet rs = pstmt.executeQuery();
									rs.next();
					
										pstmt = conn.prepareStatement("SELECT * FROM class WHERE class_id=?");
										pstmt.setInt(1,rs.getInt("class_id"));
										ResultSet rsClass = pstmt.executeQuery();
									rsClass.next();
										String course_number = rsClass.getString("course_number");
										String title = rsClass.getString("title");
										String quarter = rsClass.getString("quarter");
										int year = rsClass.getInt("year");
									
								%>
									
									Section_ID:<%=cachedMeeting.getInt("section_id")%> <%=course_number%> <%=title%>(<%=quarter%> <%=year%>)
									
								
                            	
                            	</td>
                            	
                            	<td>
								<select name="type" style="width:100px">	
									<option value="LE" <%if(cachedMeeting.getString("type").equals("LE")) out.println("selected='selected'");%>>LE</option>
									<option value="DI" <%if(cachedMeeting.getString("type").equals("DI")) out.println("selected='selected'");%>>DI</option>
									<option value="LAB" <%if(cachedMeeting.getString("type").equals("LAB")) out.println("selected='selected'");%>>LAB</option>
								</select>
								</td>
							
								<td>
								<select name="is_mandatory" style="width:100px">	
									<option value="YES" <%if(cachedMeeting.getString("is_mandatory").equals("YES")) out.println("selected='selected'");%>>YES</option>
									<option value="NO" <%if(cachedMeeting.getString("is_mandatory").equals("NO")) out.println("selected='selected'");%>>NO</option>
								</select>
								</td>		
								
								<td><input type="text" value="<%=cachedMeeting.getString("start_time")%>" name="start_time" size="10"></td>
								<td><input type="text" value="<%=cachedMeeting.getString("end_time")%>" name="end_time" size="10"></td>
								<td><input type="text" value="<%=cachedMeeting.getString("weekday")%>" name="weekday" size="10"></td>
								<td><input type="text" value="<%=cachedMeeting.getString("location")%>" name="location" size="10"></td>
                            	
                            	
                                <td><input type="submit" value="Update"></td>
                            			
                        		</form>
                        		
                        		
                        				
                        		<td>
                       				<form action="meeting.jsp" method="POST">
                           				<input type="hidden" value="delete" name="action">
                          				<input type="hidden" value="<%= cachedMeeting.getInt("meeting_id") %>" name="meeting_id">                       			
                               			<input type="submit" value="Delete">
                           			</form>
                           		</td>
                       
                    		</tr>
                    			<% 
                            	}
                            	%>
					
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							//conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							//conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>